﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using QuanLiSinhVien_v01.DTO;

namespace QuanLiSinhVien_v01.DAO
{
    class SinhVienDao: DataProvider
    {
        public SinhVienDto SinhVien { get; set; }

        protected override Object GetDataFromDataRow(DataTable dt, int rowIndex)
        {
            SinhVienDto SinhVienDto = new SinhVienDto();
            SinhVienDto.MSSV = dt.Rows[rowIndex]["MSSV"].ToString();
            SinhVienDto.HoTen = dt.Rows[rowIndex]["HoTen"].ToString();
            SinhVienDto.Email = dt.Rows[rowIndex]["Email"].ToString();
            SinhVienDto.NgaySinh = DateTime.Parse(dt.Rows[rowIndex]["NgaySinh"].ToString());
            SinhVienDto.GhiChu = dt.Rows[rowIndex]["GhiChu"].ToString();

            return SinhVienDto;
        }

        public SinhVienDto GetSinhVien()
        {
            string strCommand = " Select * from SinhVien where MSSV = '"+SinhVien.MSSV + "'";
            DataTable dt = ExcuteQuery(strCommand);
            int n = dt.Rows.Count;
            SinhVienDto[] kq = new SinhVienDto[n];
            for (int i = 0; i < n; i++)
            {
                kq[i] = (SinhVienDto)GetDataFromDataRow(dt, i);
            }
            return kq[0];
        }

        public SinhVienDto[] GetList()
        {
            string strCommand = " Select * from SinhVien ";
            DataTable dt = ExcuteQuery(strCommand);
            int n = dt.Rows.Count;
            SinhVienDto[] kq = new SinhVienDto[n];
            for (int i = 0; i < n; i++)
            {
                kq[i] = (SinhVienDto)GetDataFromDataRow(dt, i);
            }
            return kq;
        }

        public SinhVienDto[] GetDanhSachLop()
        {
            string strCommand = " Select sv.* from SinhVien_LopHoc lh , SinhVien sv  where MaLopHoc = '" + SinhVien.MaLop + "' and lh.MSSV = sv.MSSV";
            DataTable dt = ExcuteQuery(strCommand);
            int n = dt.Rows.Count;
            SinhVienDto[] kq = new SinhVienDto[n];
            for (int i = 0; i < n; i++)
            {
                kq[i] = (SinhVienDto)GetDataFromDataRow(dt, i);
            }
            return kq;
        }

        public void Insert()
        {
            string strCommand =
                "insert into SinhVien (MSSV ,HoTen ,NgaySinh , GhiChu) " +
                "values (N'" + SinhVien.MSSV + "', N'" +
                              SinhVien.HoTen + "', N'" +
                              SinhVien.NgaySinh + "', N'" +
                              SinhVien.GhiChu + "'" +
                        ")";
            try
            {
                ExcuteNonQuery(strCommand);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public bool CheckExist(string MSSV)
        {
            string strCommand = " Select * from SinhVien " +
                                "where MSSV = " + MSSV;
            DataTable dt = ExcuteQuery(strCommand);
            if (dt.Rows.Count != 0)
                return true;
            return false;
        }

        public void Update()
        {
            string strCommand =
                "update SinhVien set " +
                "HoTen = N'" + SinhVien.HoTen + "' , " +
                "NgaySinh = '" + SinhVien.NgaySinh + "' , " +
                "GhiChu = N'" + SinhVien.GhiChu + "' " +
                "where MSSV = '" + SinhVien.MSSV + "' ";
            try
            {
                ExcuteNonQuery(strCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete()
        {
            string strCommand =
                "DELETE FROM SinhVien " +
                "WHERE  MSSV = '" + SinhVien.MSSV + "'";
            try
            {
                ExcuteNonQuery(strCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public SinhVienDto[] Search(string keywork)
        {
            string strCommand = " Select * from SinhVien " +
                                "where MSSV like '%" + keywork + "%' " +
                                " or HoTen like '%" + keywork + "%' " +
                                " or NgaySinh like '%" + keywork + "%' " +
                                " or GhiChu like '%" + keywork + "%' ";
            DataTable dt = ExcuteQuery(strCommand);
            int n = dt.Rows.Count;
            SinhVienDto[] kq = new SinhVienDto[n];
            for (int i = 0; i < n; i++)
            {
                kq[i] = (SinhVienDto)GetDataFromDataRow(dt, i);
            }
            return kq;
        }


    }
}
